﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Common;
using Entities;

namespace Dao
{
    /// <summary>
    /// Provides a way of query with Post from a SQL Server Database
    /// </summary>
    public class PostData
    {
        #region Variables private
        private IList<Post> _listPost = null;
        private Post _post = null;
        #endregion

        #region Methods public
        /// <summary>
        ///  Get list information about Post
        /// </summary>
        /// <returns>List post</returns>
        public IList<Post> GetAll()
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            RepliesData replyData = new RepliesData();
            _listPost = new List<Post>();
            try
            {
                SqlDataReader reader = SQLDBHelper.ExecuteReader(conn, CommandType.StoredProcedure, "sp_Post_GetAll", null);
                while (reader.Read())
                {
                    _post = new Post();
                    if (!reader.IsDBNull(0))
                        _post.PostID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        _post.UserID = reader.GetInt32(1);
                    if (!reader.IsDBNull(2))
                        _post.PostDate = reader.GetDateTime(2);
                    if (!reader.IsDBNull(3))
                        _post.Body = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        _post.Status = reader.GetBoolean(4);
                    if (!reader.IsDBNull(5))
                        _post.Title = reader.GetString(5);
                    if (!reader.IsDBNull(6))
                        _post.CategoryID = reader.GetInt32(6);
                    if (!reader.IsDBNull(7))
                        _post.Sticky = reader.GetBoolean(7);
                    if (!reader.IsDBNull(8))
                        _post.Views = reader.GetInt32(8);
                    _listPost.Add(_post);
                }
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return _listPost;
        }
        public IList<Post> GetPostCateID(int cateID)
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            try
            {
                SqlParameter[] parm = new SqlParameter[] { new SqlParameter("@CategoryID", SqlDbType.Int, 2) };
                parm[0].Value = cateID;
                SqlDataReader reader = SQLDBHelper.ExecuteReader(conn, CommandType.StoredProcedure, "sp_Post_GetPostCateID", parm);
                _listPost = new List<Post>();
                while (reader.Read())
                {
                    _post = new Post();
                    if (!reader.IsDBNull(0))
                        _post.PostID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        _post.UserID = reader.GetInt32(1);
                    if (!reader.IsDBNull(2))
                        _post.PostDate = reader.GetDateTime(2);
                    if (!reader.IsDBNull(3))
                        _post.Body = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        _post.Status = reader.GetBoolean(4);
                    if (!reader.IsDBNull(5))
                        _post.Title = reader.GetString(5);
                    if (!reader.IsDBNull(6))
                        _post.CategoryID = reader.GetInt32(6);
                    if (!reader.IsDBNull(7))
                        _post.Sticky = reader.GetBoolean(7);
                    if (!reader.IsDBNull(8))
                        _post.Views = reader.GetInt32(8);
                    _listPost.Add(_post);
                }
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return _listPost;
        }
        /// <summary>
        /// Get details information about Post by Id of Post
        /// </summary>
        /// <param name="postId">Id of Post</param>
        /// <returns>Details of a post</returns>
        public Post GetPostById(int postId)
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            try
            {
                SqlParameter[] parm = new SqlParameter[] { new SqlParameter("@postId", SqlDbType.Int, 2) };
                parm[0].Value = postId;
                SqlDataReader reader = SQLDBHelper.ExecuteReader(conn, CommandType.StoredProcedure, "sp_Post_GetPostById", parm);
                while (reader.Read())
                {
                    _post = new Post();
                    if (!reader.IsDBNull(0))
                        _post.PostID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        _post.UserID = reader.GetInt32(1);
                    if (!reader.IsDBNull(2))
                        _post.PostDate = reader.GetDateTime(2);
                    if (!reader.IsDBNull(3))
                        _post.Body = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        _post.Status = reader.GetBoolean(4);
                    if (!reader.IsDBNull(5))
                        _post.Title = reader.GetString(5);
                    if (!reader.IsDBNull(6))
                        _post.CategoryID = reader.GetInt32(6);
                    if (!reader.IsDBNull(7))
                        _post.Sticky = reader.GetBoolean(7);
                    if (!reader.IsDBNull(8))
                        _post.Views = reader.GetInt32(8);
                }
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return _post;
        }
        /// <summary>
        /// Get details information about Post by Id of member
        /// </summary>
        /// <param name="userId">Id of member</param>
        /// <returns>Details of a post of member</returns>
        public Post GetByUserId(int userId)
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            try
            {
                SqlParameter[] parm = new SqlParameter[] { new SqlParameter("@userId", SqlDbType.Int, 2) };
                parm[0].Value = userId;
                SqlDataReader reader = SQLDBHelper.ExecuteReader(conn, CommandType.StoredProcedure, "sp_Post_GetByUserId", parm);
                while (reader.Read())
                {
                    _post = new Post();
                    if (!reader.IsDBNull(0))
                        _post.PostID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        _post.UserID = reader.GetInt32(1);
                    if (!reader.IsDBNull(2))
                        _post.PostDate = reader.GetDateTime(2);
                    if (!reader.IsDBNull(3))
                        _post.Body = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        _post.Status = reader.GetBoolean(4);
                    if (!reader.IsDBNull(5))
                        _post.Title = reader.GetString(5);
                    if (!reader.IsDBNull(6))
                        _post.CategoryID = reader.GetInt32(6);
                    if (!reader.IsDBNull(7))
                        _post.Sticky = reader.GetBoolean(7);
                    if (!reader.IsDBNull(8))
                        _post.Views = reader.GetInt32(8);
                }
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return _post;
        }

        /// <summary>
        /// Get details information about Post by post date time
        /// </summary>
        /// <param name="postDate">Date time post</param>
        /// <returns>Details of a post of member</returns>
        public Post GetByDate(DateTime postDate)
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            try
            {
                SqlParameter[] parm = new SqlParameter[] { new SqlParameter("@postDate", SqlDbType.Int, 2) };
                parm[0].Value = postDate;
                SqlDataReader reader = SQLDBHelper.ExecuteReader(conn, CommandType.StoredProcedure, "sp_Post_GetByDate", parm);
                while (reader.Read())
                {
                    _post = new Post();
                    if (!reader.IsDBNull(0))
                        _post.PostID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        _post.UserID = reader.GetInt32(1);
                    if (!reader.IsDBNull(2))
                        _post.PostDate = reader.GetDateTime(2);
                    if (!reader.IsDBNull(3))
                        _post.Body = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        _post.Status = reader.GetBoolean(4);
                    if (!reader.IsDBNull(5))
                        _post.Title = reader.GetString(5);
                    if (!reader.IsDBNull(6))
                        _post.CategoryID = reader.GetInt32(6);
                    if (!reader.IsDBNull(7))
                        _post.Sticky = reader.GetBoolean(7);
                    if (!reader.IsDBNull(8))
                        _post.Views = reader.GetInt32(8);
                }
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return _post;
        }
        /// <summary>
        /// Get details information about Post by status is unread or read
        /// </summary>
        /// <param name="status">False get unread, true get read</param>
        /// <returns>Details post was unread or read</returns>
        public Post GetByStatus(bool status)
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            try
            {
                SqlParameter[] parm = new SqlParameter[] { new SqlParameter("@status", SqlDbType.Bit) };
                parm[0].Value = status;
                SqlDataReader reader = SQLDBHelper.ExecuteReader(conn, CommandType.StoredProcedure, "sp_Post_GetByStatus", parm);
                while (reader.Read())
                {
                    _post = new Post();
                    if (!reader.IsDBNull(0))
                        _post.PostID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        _post.UserID = reader.GetInt32(1);
                    if (!reader.IsDBNull(2))
                        _post.PostDate = reader.GetDateTime(2);
                    if (!reader.IsDBNull(3))
                        _post.Body = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        _post.Status = reader.GetBoolean(4);
                    if (!reader.IsDBNull(5))
                        _post.Title = reader.GetString(5);
                    if (!reader.IsDBNull(6))
                        _post.CategoryID = reader.GetInt32(6);
                    if (!reader.IsDBNull(7))
                        _post.Sticky = reader.GetBoolean(7);
                    if (!reader.IsDBNull(8))
                        _post.Views = reader.GetInt32(8);
                }
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return _post;
        }
        /// <summary>
        /// Insert or Update a post
        /// If PostID = 1 then Insert a post
        /// If PostID != 1 then Update a post
        /// </summary>
        /// <param name="post">colection properties of class Post want to update or insert</param>
        /// <returns>True is successful , False is unsuccessful</returns>
        public bool InsertOrUpdate(Post post)
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            int result = 0;
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                                new SqlParameter("@PostID",SqlDbType.Int,2),    
                                new SqlParameter("@UserID", SqlDbType.Int, 2),
                                new SqlParameter("@PostDate", SqlDbType.DateTime),
                                new SqlParameter("@Body", SqlDbType.NText),
                                new SqlParameter("@Status", SqlDbType.Bit),
                                new SqlParameter("@Title", SqlDbType.NVarChar,250),
                                new SqlParameter("@CategoryID", SqlDbType.Int),
                                new SqlParameter("@Sticky", SqlDbType.Bit),
                                new SqlParameter("@Views", SqlDbType.Int)
                };

                parms[0].Value = post.PostID;
                parms[1].Value = post.UserID;
                parms[2].Value = post.PostDate;
                parms[3].Value = post.Body;
                parms[4].Value = post.Status;
                parms[5].Value = post.Title;
                parms[6].Value = post.CategoryID;
                parms[7].Value = post.Sticky;
                parms[8].Value = post.Views;
                if (post.PostID == 1)
                    result = SQLDBHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "sp_Post_Update", parms);
                else
                    result = SQLDBHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "sp_Post_Insert", parms);
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return result > 0 ? true : false;
        }
        /// <summary>
        /// Delete a post of member
        /// </summary>
        /// <param name="postId">Id of post want to delete</param>
        /// <returns>True is successful , False is unsuccessful</returns>
        public bool DeleteById(int postId)
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            int result = 0;
            try
            {
                SqlParameter[] parm = new SqlParameter[] { new SqlParameter("@postId", SqlDbType.Int, 2) };
                parm[0].Value = postId;
                result = SQLDBHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "sp_Post_DeleteById", parm);
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return result > 0 ? true : false;
        }
        #endregion
    }
}
